package cn.edu.xmu.javaee.productdemoaop.mapper.join;

import cn.edu.xmu.javaee.productdemoaop.mapper.generator.ProductPoSqlProvider;
import cn.edu.xmu.javaee.productdemoaop.mapper.generator.po.OnSalePo;
import cn.edu.xmu.javaee.productdemoaop.mapper.generator.po.ProductPo;
import cn.edu.xmu.javaee.productdemoaop.mapper.generator.po.ProductPoExample;
import cn.edu.xmu.javaee.productdemoaop.mapper.join.po.ProductAllJoinPo;
import cn.edu.xmu.javaee.productdemoaop.mapper.manual.po.ProductAllPo;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.type.JdbcType;

import java.util.List;

@Mapper
public interface ProductAllJoinMapper {
    @Select({
            "SELECT gp.id, gp.sku_sn, gp.name, gp.original_price, gp.weight, ",
            "gp.barcode, gp.unit, gp.origin_place, gp.commission_ratio, gp.free_threshold, ",
            "gp.status, gp.creator_id, gp.creator_name, gp.modifier_id, gp.modifier_name, ",
            "gp.gmt_create, gp.gmt_modified, ",
            "p.id AS p_id, p.sku_sn AS p_sku_sn, p.name AS p_name, p.original_price AS p_original_price, ",
            "p.weight AS p_weight, p.barcode AS p_barcode, p.unit AS p_unit, p.origin_place AS p_origin_place, ",
            "p.creator_id AS p_creator_id, p.creator_name AS p_creator_name, p.modifier_id AS p_modifier_id, ",
            "p.modifier_name AS p_modifier_name, p.gmt_create AS p_gmt_create, p.gmt_modified AS p_gmt_modified, ",
            "o.id AS o_id, o.product_id AS o_product_id, o.price AS o_price, o.begin_time AS o_begin_time, ",
            "o.end_time AS o_end_time, o.quantity AS o_quantity, o.max_quantity AS o_max_quantity, ",
            "o.creator_id AS o_creator_id, o.creator_name AS o_creator_name, o.modifier_id AS o_modifier_id, ",
            "o.modifier_name AS o_modifier_name, o.gmt_create AS o_gmt_create, o.gmt_modified AS o_gmt_modified ",
            "FROM goods_product gp ",
            "LEFT JOIN goods_product p ON gp.goods_id = p.goods_id ",
            "LEFT JOIN goods_onsale o ON gp.id = o.product_id ",
            "WHERE o.begin_time <= NOW() AND o.end_time > NOW() ",
            "AND gp.name = #{name}"
    })
    @Results({
            @Result(column="id", property="id", jdbcType=JdbcType.BIGINT, id=true),
            @Result(column="sku_sn", property="skuSn", jdbcType=JdbcType.VARCHAR),
            @Result(column="name", property="name", jdbcType=JdbcType.VARCHAR),
            @Result(column="original_price", property="originalPrice", jdbcType=JdbcType.BIGINT),
            @Result(column="weight", property="weight", jdbcType=JdbcType.BIGINT),
            @Result(column="barcode", property="barcode", jdbcType=JdbcType.VARCHAR),
            @Result(column="unit", property="unit", jdbcType=JdbcType.VARCHAR),
            @Result(column="origin_place", property="originPlace", jdbcType=JdbcType.VARCHAR),
            @Result(column="commission_ratio", property="commissionRatio", jdbcType=JdbcType.INTEGER),
            @Result(column="free_threshold", property="freeThreshold", jdbcType=JdbcType.BIGINT),
            @Result(column="status", property="status", jdbcType=JdbcType.SMALLINT),
            @Result(column="creator_id", property="creatorId", jdbcType=JdbcType.BIGINT),
            @Result(column="creator_name", property="creatorName", jdbcType=JdbcType.VARCHAR),
            @Result(column="modifier_id", property="modifierId", jdbcType=JdbcType.BIGINT),
            @Result(column="modifier_name", property="modifierName", jdbcType=JdbcType.VARCHAR),
            @Result(column="gmt_create", property="gmtCreate", jdbcType=JdbcType.TIMESTAMP),
            @Result(column="gmt_modified", property="gmtModified", jdbcType=JdbcType.TIMESTAMP),
            @Result(property =  "onSaleList", many =@Many(resultMap = "onSale", columnPrefix = "o_")),
            @Result(property =  "otherProduct", many =@Many(resultMap = "otherProduct", columnPrefix = "p_"))
    })
    List<ProductAllJoinPo> getProductWithJoin(@Param("name") String name);

    @Select({
            "SELECT gp.id, gp.sku_sn, gp.name, gp.original_price, gp.weight, ",
            "gp.barcode, gp.unit, gp.origin_place, gp.commission_ratio, gp.free_threshold, ",
            "gp.status, gp.creator_id, gp.creator_name, gp.modifier_id, gp.modifier_name, ",
            "gp.gmt_create, gp.gmt_modified, ",
            "p.id AS p_id, p.sku_sn AS p_sku_sn, p.name AS p_name, p.original_price AS p_original_price, ",
            "p.weight AS p_weight, p.barcode AS p_barcode, p.unit AS p_unit, p.origin_place AS p_origin_place, ",
            "p.creator_id AS p_creator_id, p.creator_name AS p_creator_name, p.modifier_id AS p_modifier_id, ",
            "p.modifier_name AS p_modifier_name, p.gmt_create AS p_gmt_create, p.gmt_modified AS p_gmt_modified, ",
            "o.id AS o_id, o.product_id AS o_product_id, o.price AS o_price, o.begin_time AS o_begin_time, ",
            "o.end_time AS o_end_time, o.quantity AS o_quantity, o.max_quantity AS o_max_quantity, ",
            "o.creator_id AS o_creator_id, o.creator_name AS o_creator_name, o.modifier_id AS o_modifier_id, ",
            "o.modifier_name AS o_modifier_name, o.gmt_create AS o_gmt_create, o.gmt_modified AS o_gmt_modified ",
            "FROM goods_product gp ",
            "LEFT JOIN goods_product p ON gp.goods_id = p.goods_id ",
            "LEFT JOIN goods_onsale o ON gp.id = o.product_id ",
            "WHERE o.begin_time <= NOW() AND o.end_time > NOW() ",
            "AND gp.id = #{id} "
    })
    @Results({
            @Result(column="id", property="id", jdbcType=JdbcType.BIGINT, id=true),
            @Result(column="sku_sn", property="skuSn", jdbcType=JdbcType.VARCHAR),
            @Result(column="name", property="name", jdbcType=JdbcType.VARCHAR),
            @Result(column="original_price", property="originalPrice", jdbcType=JdbcType.BIGINT),
            @Result(column="weight", property="weight", jdbcType=JdbcType.BIGINT),
            @Result(column="barcode", property="barcode", jdbcType=JdbcType.VARCHAR),
            @Result(column="unit", property="unit", jdbcType=JdbcType.VARCHAR),
            @Result(column="origin_place", property="originPlace", jdbcType=JdbcType.VARCHAR),
            @Result(column="commission_ratio", property="commissionRatio", jdbcType=JdbcType.INTEGER),
            @Result(column="free_threshold", property="freeThreshold", jdbcType=JdbcType.BIGINT),
            @Result(column="status", property="status", jdbcType=JdbcType.SMALLINT),
            @Result(column="creator_id", property="creatorId", jdbcType=JdbcType.BIGINT),
            @Result(column="creator_name", property="creatorName", jdbcType=JdbcType.VARCHAR),
            @Result(column="modifier_id", property="modifierId", jdbcType=JdbcType.BIGINT),
            @Result(column="modifier_name", property="modifierName", jdbcType=JdbcType.VARCHAR),
            @Result(column="gmt_create", property="gmtCreate", jdbcType=JdbcType.TIMESTAMP),
            @Result(column="gmt_modified", property="gmtModified", jdbcType=JdbcType.TIMESTAMP),
            @Result(property =  "onSaleList", many =@Many(resultMap = "onSale", columnPrefix = "o_")),
            @Result(property =  "otherProduct", many =@Many(resultMap = "otherProduct", columnPrefix = "p_"))
    })
    List<ProductAllJoinPo> getProductWithJoin_id(@Param("id") Long id);

    /*@Select({
            "SELECT",
            "`id`, `product_id`, `price`, `begin_time`, `end_time`, `quantity`, `max_quantity`, `creator_id`, ",
            "`creator_name`, `modifier_id`, `modifier_name`, `gmt_create`, `gmt_modified`",
            "FROM goods_onsale",
            "WHERE `product_id` = #{productId,jdbcType=BIGINT} AND `begin_time` <= NOW() AND `end_time` > NOW()"
    })*/
    @Select({"SELECT id FROM goods_onsale WHERE id = #{id}"})
    @Results(id = "onSale", value = {
            @Result(column="id", property="id", jdbcType=JdbcType.BIGINT, id=true),
            @Result(column="product_id", property="productId", jdbcType=JdbcType.BIGINT),
            @Result(column="price", property="price", jdbcType=JdbcType.BIGINT),
            @Result(column="begin_time", property="beginTime", jdbcType=JdbcType.TIMESTAMP),
            @Result(column="end_time", property="endTime", jdbcType=JdbcType.TIMESTAMP),
            @Result(column="quantity", property="quantity", jdbcType=JdbcType.INTEGER),
            @Result(column="max_quantity", property="maxQuantity", jdbcType=JdbcType.INTEGER),
            @Result(column="creator_id", property="creatorId", jdbcType=JdbcType.BIGINT),
            @Result(column="creator_name", property="creatorName", jdbcType=JdbcType.VARCHAR),
            @Result(column="modifier_id", property="modifierId", jdbcType=JdbcType.BIGINT),
            @Result(column="modifier_name", property="modifierName", jdbcType=JdbcType.VARCHAR),
            @Result(column="gmt_create", property="gmtCreate", jdbcType=JdbcType.TIMESTAMP),
            @Result(column="gmt_modified", property="gmtModified", jdbcType=JdbcType.TIMESTAMP)
    })
    List<OnSalePo> selectLastOnSaleByProductId(Long id);

    @Select({"SELECT id FROM goods_product WHERE id = #{id}"})
    @Results(id = "otherProduct", value = {
            @Result(column="id", property="id", jdbcType=JdbcType.BIGINT, id=true),
            @Result(column="sku_sn", property="skuSn", jdbcType=JdbcType.VARCHAR),
            @Result(column="name", property="name", jdbcType=JdbcType.VARCHAR),
            @Result(column="original_price", property="originalPrice", jdbcType=JdbcType.BIGINT),
            @Result(column="weight", property="weight", jdbcType=JdbcType.BIGINT),
            @Result(column="barcode", property="barcode", jdbcType=JdbcType.VARCHAR),
            @Result(column="unit", property="unit", jdbcType=JdbcType.VARCHAR),
            @Result(column="origin_place", property="originPlace", jdbcType=JdbcType.VARCHAR),
            @Result(column="creator_id", property="creatorId", jdbcType=JdbcType.BIGINT),
            @Result(column="creator_name", property="creatorName", jdbcType=JdbcType.VARCHAR),
            @Result(column="modifier_id", property="modifierId", jdbcType=JdbcType.BIGINT),
            @Result(column="modifier_name", property="modifierName", jdbcType=JdbcType.VARCHAR),
            @Result(column="gmt_create", property="gmtCreate", jdbcType=JdbcType.TIMESTAMP),
            @Result(column="gmt_modified", property="gmtModified", jdbcType=JdbcType.TIMESTAMP)
    })
    ProductPo selectOtherProduct(Long goodsId);
}
